﻿using Dapper;
using NHibernate;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Dynamic;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;

namespace YYSite.Common
{
    public static class SqlDapperExtension
    {
        private static readonly ConcurrentDictionary<Type, List<PropertyInfo>> _paramCache = new ConcurrentDictionary<Type, List<PropertyInfo>>();

        /// <summary>Insert data into table.
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="data"></param>
        /// <param name="table"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public static long Insert(this IDbConnection connection, dynamic data, string table, IDbTransaction transaction = null, int? commandTimeout = null)
        {
            var obj = data as object;
            var properties = GetProperties(obj);
            var columns = string.Join(",", properties);
            var values = string.Join(",", properties.Select(p => "@" + p));
            var sql = string.Format("insert into {0} ({1}) values ({2}) select cast(scope_identity() as bigint)", table, columns, values);

            return connection.ExecuteScalar<long>(sql, obj, transaction, commandTimeout);
        }


        /// <summary>Updata data for table with a specified condition.
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="data"></param>
        /// <param name="condition"></param>
        /// <param name="table"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public static int Update(this IDbConnection connection, dynamic data, dynamic condition, string table, IDbTransaction transaction = null, int? commandTimeout = null)
        {
            var obj = data as object;
            var conditionObj = condition as object;

            var updatePropertyInfos = GetPropertyInfos(obj);
            var wherePropertyInfos = GetPropertyInfos(conditionObj);

            var updateProperties = updatePropertyInfos.Select(p => p.Name);
            var whereProperties = wherePropertyInfos.Select(p => p.Name);

            var updateFields = string.Join(",", updateProperties.Select(p => p + " = @" + p));
            var whereFields = string.Empty;

            if (whereProperties.Any())
            {
                whereFields = " where " + string.Join(" and ", whereProperties.Select(p => p + " = @w_" + p));
            }

            var sql = string.Format("update {0} set {1}{2}", table, updateFields, whereFields);

            var parameters = new DynamicParameters(data);
            var expandoObject = new ExpandoObject() as IDictionary<string, object>;
            wherePropertyInfos.ForEach(p => expandoObject.Add("w_" + p.Name, p.GetValue(conditionObj, null)));
            parameters.AddDynamicParams(expandoObject);

            return connection.Execute(sql, parameters, transaction, commandTimeout);
        }


        /// <summary>Delete data from table with a specified condition.
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="condition"></param>
        /// <param name="table"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public static int Delete(this IDbConnection connection, dynamic condition, string table, IDbTransaction transaction = null, int? commandTimeout = null)
        {
            var conditionObj = condition as object;
            var whereFields = string.Empty;
            var whereProperties = GetProperties(conditionObj);
            if (whereProperties.Count > 0)
            {
                whereFields = " where " + string.Join(" and ", whereProperties.Select(p => p + " = @" + p));
            }

            var sql = string.Format("delete from {0}{1}", table, whereFields);

            return connection.Execute(sql, conditionObj, transaction, commandTimeout);
        }


        /// <summary>Get data count from table with a specified condition.
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="condition"></param>
        /// <param name="table"></param>
        /// <param name="isOr"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public static int GetCount(this IDbConnection connection, object condition, string table, bool isOr = false, IDbTransaction transaction = null, int? commandTimeout = null)
        {
            return QueryList<int>(connection, condition, table, "count(*)", isOr, transaction, commandTimeout).Single();
        }


        /// <summary>Query a list of data from table with a specified condition.
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="condition"></param>
        /// <param name="table"></param>
        /// <param name="columns"></param>
        /// <param name="isOr"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public static IEnumerable<dynamic> QueryList(this IDbConnection connection, dynamic condition, string table, string columns = "*", bool isOr = false, IDbTransaction transaction = null, int? commandTimeout = null)
        {
            return QueryList<dynamic>(connection, condition, table, columns, isOr, transaction, commandTimeout);
        }

        /// <summary>Query a list of data from table with specified condition.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connection"></param>
        /// <param name="condition"></param>
        /// <param name="table"></param>
        /// <param name="columns"></param>
        /// <param name="isOr"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public static IEnumerable<T> QueryList<T>(this IDbConnection connection, object condition, string table, string columns = "*", bool isOr = false, IDbTransaction transaction = null, int? commandTimeout = null)
        {
            return connection.Query<T>(BuildQuerySQL(condition, table, columns, isOr), condition, transaction, true, commandTimeout);
        }


        /// <summary>Query paged data from a single table.
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="condition"></param>
        /// <param name="table"></param>
        /// <param name="orderBy"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="columns"></param>
        /// <param name="isOr"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public static IEnumerable<dynamic> QueryPaged(this IDbConnection connection, dynamic condition, string table, string orderBy, int pageIndex, int pageSize, string columns = "*", bool isOr = false, IDbTransaction transaction = null, int? commandTimeout = null)
        {
            return QueryPaged<dynamic>(connection, condition, table, orderBy, pageIndex, pageSize, columns, isOr, transaction, commandTimeout);
        }

        /// <summary>Query paged data from a single table.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connection"></param>
        /// <param name="condition"></param>
        /// <param name="table"></param>
        /// <param name="orderBy"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="columns"></param>
        /// <param name="isOr"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public static IEnumerable<T> QueryPaged<T>(this IDbConnection connection, dynamic condition, string table, string orderBy, int pageIndex, int pageSize, string columns = "*", bool isOr = false, IDbTransaction transaction = null, int? commandTimeout = null)
        {
            var conditionObj = condition as object;
            var whereFields = string.Empty;
            var properties = GetProperties(conditionObj);
            if (properties.Count > 0)
            {
                var separator = isOr ? " OR " : " AND ";
                whereFields = " WHERE " + string.Join(separator, properties.Select(p => p + " = @" + p));
            }
            var sql = string.Format("SELECT {0} FROM (SELECT ROW_NUMBER() OVER (ORDER BY {1}) AS RowNumber, {0} FROM {2}{3}) AS Total WHERE RowNumber >= {4} AND RowNumber <= {5}", columns, orderBy, table, whereFields, (pageIndex - 1) * pageSize + 1, pageIndex * pageSize);

            return connection.Query<T>(sql, conditionObj, transaction, true, commandTimeout);
        }


        private static string BuildQuerySQL(dynamic condition, string table, string selectPart = "*", bool isOr = false)
        {
            var conditionObj = condition as object;
            var properties = GetProperties(conditionObj);
            if (properties.Count == 0)
            {
                return string.Format("SELECT {1} FROM {0}", table, selectPart);
            }

            var separator = isOr ? " OR " : " AND ";
            var wherePart = string.Join(separator, properties.Select(p => p + " = @" + p));

            return string.Format("SELECT {2} FROM {0} WHERE {1}", table, wherePart, selectPart);
        }
        private static List<string> GetProperties(object obj)
        {
            if (obj == null)
            {
                return new List<string>();
            }
            if (obj is DynamicParameters)
            {
                return (obj as DynamicParameters).ParameterNames.ToList();
            }
            return GetPropertyInfos(obj).Select(x => x.Name).ToList();
        }
        private static List<PropertyInfo> GetPropertyInfos(object obj)
        {
            if (obj == null)
            {
                return new List<PropertyInfo>();
            }

            List<PropertyInfo> properties;
            if (_paramCache.TryGetValue(obj.GetType(), out properties)) return properties.ToList();
            properties = obj.GetType().GetProperties(BindingFlags.GetProperty | BindingFlags.Instance | BindingFlags.Public).ToList();
            _paramCache[obj.GetType()] = properties;
            return properties;
        }

        /// <summary>
        /// 复杂分页
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connection"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <param name="orderBy"></param>
        /// <returns></returns>
        public static DataGridOutput<T> QueryPaged<T>(this ISession session, string sql, SqlDynamicParameters parameters,string orderBy, DataGridInput input)
        {
            string sqls = @"SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY {0}) AS ROWID,* FROM ({1}  ) AS T ) AS TT WHERE ROWID BETWEEN @StartRecordIndex AND @EndRecordIndex
                SELECT COUNT(*)  FROM ({1} ) AS T";
   
            sqls = string.Format(sqls, orderBy, sql);

            parameters.Add("@StartRecordIndex", input.GetStartRecordIndex(), DbType.Int32, 11);
            parameters.Add("@EndRecordIndex", input.GetEndRecordIndex(), DbType.Int32, 11);

            session.Transaction.Dispose();
            var mutil = session.Connection.QueryMultiple(sqls, parameters);
            var data = mutil.Read<T>();
            var total = mutil.Read<int>().FirstOrDefault();
  

            return new DataGridOutput<T>(data, total, input.GetPageIndex(), input.GetPageSize());


        }

        /// <summary>
        /// 复杂分页
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connection"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <param name="orderBy"></param>
        /// <returns></returns>
        public static DataGridOutput<T> QueryPagedComplex<T>(this IDbConnection connection, int pageIndex, int pageSize, string sql, SqlDynamicParameters parameters, string orderBy)
        {
            string sqls = "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY " + orderBy + ") AS ROWID,* FROM (" + sql + @") AS T ) AS TT WHERE ROWID BETWEEN @StartRecordIndex AND @EndRecordIndex
                SELECT COUNT(*)  FROM (" + sql + ") AS T";
            int startRecordIndex = (pageIndex - 1) * pageSize + 1;
            if (startRecordIndex < 1)
                startRecordIndex = 1;
            startRecordIndex += 1;
            int endRecordIndex = startRecordIndex + pageSize;
            if (endRecordIndex <= 0)
                endRecordIndex = 10;
            if (parameters == null)
                parameters = new SqlDynamicParameters();
            parameters.Add("@StartRecordIndex", startRecordIndex, DbType.Int32, 11);
            parameters.Add("@EndRecordIndex", endRecordIndex, DbType.Int32, 11);

            var mutil = connection.QueryMultiple(sqls, parameters);
            var data = mutil.Read<T>();
            var total = mutil.Read<int>().FirstOrDefault();
            connection.Close();
            return new DataGridOutput<T>(data, total, pageIndex, pageSize);


        }
        /// <summary>
        /// 复杂分页
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connection"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <param name="orderBy"></param>
        /// <returns></returns>
        public static async Task<DataGridOutput<T>> QueryPagedComplexAsync<T>(this IDbConnection connection, int pageIndex, int pageSize, string sql, SqlDynamicParameters parameters, string orderBy)
        {
            string sqls = "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY " + orderBy + ") AS ROWID,* FROM (" + sql + @") AS T ) AS TT WHERE ROWID BETWEEN @StartRecordIndex AND @EndRecordIndex
                SELECT COUNT(*)  FROM (" + sql + ") AS T";
            int startRecordIndex = (pageIndex - 1) * pageSize + 1;
            if (startRecordIndex <= 1)
                startRecordIndex = 1;
            startRecordIndex += 1;
            int endRecordIndex = startRecordIndex + pageSize;
            if (endRecordIndex <= 0)
                endRecordIndex = 10;
            if (parameters == null)
                parameters = new SqlDynamicParameters();
            parameters.Add("@StartRecordIndex", startRecordIndex, DbType.Int32);
            parameters.Add("@EndRecordIndex", endRecordIndex, DbType.Int32);

            var mutil = await connection.QueryMultipleAsync(sqls, parameters);
            var data = mutil.Read<T>();
            var total = mutil.Read<int>().FirstOrDefault();
            connection.Close();
            return new DataGridOutput<T>(data, total, pageIndex, pageSize);


        }
        /// <summary>
        /// 批量写入数据【自带事物提交】
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="dataTable"></param>
        public static void BulkCopy(this SqlConnection connection, DataTable dataTable)
        {

            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            SqlTransaction transaction = (SqlTransaction)connection.BeginTransaction();
            SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.CheckConstraints, transaction);
            try
            {
                sqlBulkCopy.DestinationTableName = dataTable.TableName;
                sqlBulkCopy.WriteToServer(dataTable);
                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
        /// <summary>
        /// 批量写入数据【自带事物提交】
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="dataset"></param>
        public static void BulkCopy(this SqlConnection connection, DataSet dataset)
        {

            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            SqlTransaction transaction = (SqlTransaction)connection.BeginTransaction();
            SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.CheckConstraints, transaction);
            try
            {
                foreach (DataTable dataTable in dataset.Tables)
                {
                    sqlBulkCopy.DestinationTableName = dataTable.TableName;
                    sqlBulkCopy.WriteToServer(dataTable);
                }

                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// 批量写入数据【调用时手动提交事务方便和其他方法混合使用】
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="dataTable"></param>
        public static void BulkCopy(this SqlTransaction transaction, DataTable dataTable)
        {
            if (transaction.Connection.State == ConnectionState.Closed)
            {
                transaction.Connection.Open();
            }
            SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(transaction.Connection, SqlBulkCopyOptions.CheckConstraints, transaction);

            sqlBulkCopy.DestinationTableName = dataTable.TableName;
            sqlBulkCopy.WriteToServer(dataTable);
        }
        /// <summary>
        /// 批量写入数据【调用时手动提交事务方便和其他方法混合使用】
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="dataset"></param>
        public static void BulkCopy(this SqlTransaction transaction, DataSet dataset)
        {

            if (transaction.Connection.State == ConnectionState.Closed)
            {
                transaction.Connection.Open();
            }

            SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(transaction.Connection, SqlBulkCopyOptions.CheckConstraints, transaction);

            foreach (DataTable dataTable in dataset.Tables)
            {
                sqlBulkCopy.DestinationTableName = dataTable.TableName;
                sqlBulkCopy.WriteToServer(dataTable);
            }


        }

    }
}

